<?php 
class report extends Controller {

	function report()
	{
		parent::Controller();
		date_default_timezone_set ("Asia/Bangkok"); 	
		if(!$this->user->is_login())
		{
			redirect('login', 'location'); 
		}
		$this->form_validation->set_message('required', 'กรุณาระบุ %s');
		$this->form_validation->set_message('is_natural_no_zero', 'กรุณาระบุ %s');
		//$this->output->enable_profiler(TRUE);
	}
	function dashboard()
	{
		$this->template->load('admin/themes',"report/dashboard",null);
		
		
	}
	function maintain_company($id = 0,$offset =0)
	{
			if(!$id)
			{
				$this->form_validation->set_rules('company_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/maintain_company_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["company_id"],"location");
					
				}
			}
			else
			{
				
				$base = $this->user->current_controller()."/".$id;
				$this->db->where("company_id",$id);
				$cats =$this->db->get("company")->result_array();
				
				$data["head"] ="<h3>".$cats[0]["company_name"]."</h3>";
				$sql ="select * from maintain_history_views  where company_id = $id";
				$this->_page_query($sql,$base,"report/maintain_company_report",$data,true,30);
			}
	}
	function budget1($budget_id = 0)
	{
			if(!$budget_id)
			{
				$this->form_validation->set_rules('budget_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/budget_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["budget_id"]."/","location");
					
				}
			}
			else
			{
				
					$base = $this->user->current_controller()."/".$budget_id;
					
					$this->db->where("budget_id",$budget_id);
					$cats =$this->db->get("budgets")->result_array();
					
					$data["head"] ="<h3>".$cats[0]["budget_name"]."</h3>";
					$sql ="select * from  material_views where type_id = 2 and budget_id = $budget_id";
					$this->_page_query($sql,$base,"report/report",$data,true,30);
			}
	}
	function budget2($budget_id = 0)
	{
			if(!$budget_id)
			{
				$this->form_validation->set_rules('budget_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/budget_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["budget_id"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$budget_id;
					
					$this->db->where("budget_id",$budget_id);
					$cats =$this->db->get("budgets")->result_array();
					
					$data["head"] ="<h3>".$cats[0]["budget_name"]."</h3>";
					$sql ="select * from  material_views where type_id = 2 and budget_id = $budget_id";
					$this->_page_query($sql,$base,"report/report",0,$data,true,30);
			}
	}
	function page1()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page2()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page3()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page4()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page5()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page6()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page7()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page8()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page9()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page10()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page11()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page12()
	{
		$this->template->load("admin/themes","report/page",null);
	}	
	function index($offset=0)
	{
		
		 $condition = "";
		if($filter = $this->session->userdata("report_filter"))
		{
		if($filter["category_id"])
			{
			$condition = " and materials.category_id = ".$filter["category_id"];
			}
			if($filter["type_id"])
			{
			$condition = " and materials.category_id = ".$filter["type_id"];
			}
			if($filter["year"])
			{
			$condition = " and budgets.year = '".$filter["year"].".'";
			}
			
			if($filter["budget_id"])
			{
			$condition = " and budgets.budget_id = ".$filter["budget_id"];
			}
			
			if($filter["company_id"])
			{
			$condition = " and materials.company_id = ".$filter["company_id"];
			}
			
			if($filter["place_id"])
			{
			$condition = " and materials.place_id = ".$filter["place_id"];
			}
		}
		else
		{
			$filter =  Array ( "type_id" => 0, "category_id" => 0, "budget_id" => 0 ,"place_id" => 0 ,"company_id" => 0, "year" => 0 ) ;
			$this->session->set_userdata("report_filter");
		}
			
		
		$sql = "SELECT
		ref_code,
		materials.material_id,
		materials.`name`,
		materials.model,
		materials.`code`,
		materials.category_id,
		materials.buy_date,
		materials.buy_price,
		materials.brand,
		materials.warranty,
		materials.detail,
		materials.responsible,
		materials.thumbnail,
		materials.image2,
		materials.create_date,
		materials.last_modify,
		materials.`year`,
		materials.budget_id,
		materials.place_id,
		materials.company_id,
		budgets.budget_name,
		budgets.`year`,
		company.company_name,
		company.address,
		company.tel,
		place.place_name,
		place.remark,
		material_types.type_name,
		material_categories.category_name
		FROM
		materials
		LEFT JOIN budgets ON materials.budget_id = budgets.budget_id
		LEFT JOIN company ON materials.company_id = company.company_id
		left JOIN place ON materials.place_id = place.place_id
		LEFT JOIN material_types ON materials.type_id = material_types.type_id
		left JOIN material_categories ON materials.category_id = material_categories.category_id
		where 1  $condition"; 
				$base = "report/index";
		
		$this->_page_query($sql,$base,"report/report",$offset,array(),true,50);
		
	
	}
	function report_filter()
	{
		
		$this->session->set_userdata("report_filter",$_POST);
		redirect('report/index', 'location');
	
	}
function _page_query($sql,$base,$view="",$offset=0,$data= array(),$show =true,$limit = 9)
	{
		     
		$config['base_url'] = base_url()."index.php/$base/";
		$config['per_page'] = $limit;
		$config['total_rows'] = count($this->db->query($sql)->result_array());
		$this->pagination->initialize($config);
		
		$data['page_link'] =$this->pagination->create_links();
		$data['total'] = ceil( $config['total_rows']/$limit) ;
		
		$data['current_page'] = 1;
		
		if($offset){
		$data['current_page'] = ($offset/$limit)+1; 
		}
		
		$sql .=" limit $offset,$limit"; 
		$data['page'] =$this->pagination;
		
		$data['rows'] = $this->db->query($sql)->result_array();
		
		
		
		$data['paging'] = $this->load->view("paging",$data,true);	
		
		
		if($show){
		$this->template->load("admin/themes",$view,$data);
		}else
		{
			return $data;
		}
		
	}
function material2_by_year($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_views where type_id = 2 and year_value = $year";
					$this->_page_query($sql,$base,"report/report",0,$data,true,30);
			}
}
function summary($year=0)
{
	if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_summary_views where  year_value = $year";
					$this->_page_query($sql,$base,"report/summary",0,$data,true,30);
			}
}
function m_request_by_year1($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_request_views where type_id = 1 and year_value = $year";
					$this->_page_query($sql,$base,"admin/m_request_list",0,$data,true,30);
			}
}
function m_request_by_year2($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_request_views where type_id = 2 and year_value = $year";
					$this->_page_query($sql,$base,"admin/m_request_list",0,$data,true,30);
			}
}
function material1_by_year($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_views where type_id = 1 and year_value = $year";
					$this->_page_query($sql,$base,"report/report",0,$data,true,30);
			}
}
function table($id,$param1=null)
    {
    	$this->db->where("rb_id",$id);
    	
    	$rows = $this->db->get("report_builders")->result_array();
    	$data["row"] = $rows[0];
    	$codition ="";
    	$data["param1"] = "";
    	if(!is_null($param1))
    	{
    		$codition = " and ".str_ireplace("{param1}", $param1, $rows[0]["param"]);
    		$data["param1"] = $param1;
    	}
    	
    	
    	
    	$this->load->library('Tablebuilder');
    	
    	
    	
    	$sql = $rows[0]["sql_text"].$codition;
		$this->tablebuilder->set_query($sql);
		$data["result"] = 	$this->tablebuilder->get_table();
		$this->template->load('admin/themes',"report_builder/view",$data);
		
    }
    function export($method)
    {
    	
    	$this->db->where("webservice_method",$method);
    	$rows = $this->db->get("report_builders")->result_array();
    	$data["row"] = $rows[0];
    	$this->load->library('Tablebuilder');
		$this->tablebuilder->set_query($rows[0]["sql_text"]);
		header('Content-type: text/xml');
		echo  	$this->tablebuilder->get_xml();
		
    	
    }
    function add_report()
    {
    		$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql_text',  "sql text", 'trim|required');
			$this->form_validation->set_rules('report_name',  "Report name", 'trim|required');
			
			$this->form_validation->set_rules('webservice_method',  "", 'trim');
			$data = array();
			if ($this->form_validation->run() == FALSE)
			{
				$this->template->load('admin/themes',"report_builder/add_report");
			}
			else
			{
					$this->db->insert("report_builders",$_POST);
					$this->user->back_action();
			
			}
			
    }
   	function report_edit($id)
   	{
   		
   	$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql_text',  "sql text", 'trim|required');
			$this->form_validation->set_rules('report_name',  "Report name", 'trim|required');
			
			$this->form_validation->set_rules('webservice_method',  "", 'trim');
			$data = array();
			$this->db->where("rb_id",$id);
			if ($this->form_validation->run() == FALSE)
			{
				$rows = $this->db->get("report_builders")->result_array();
				$data["row"] = $rows[0];
				$this->template->load('admin/themes',"report_builder/report_edit",$data);
			}
			else
			{
					$this->db->update("report_builders",$_POST);
					$this->user->back_action();
			
			}
   	}
	function query_form()
	{
			$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql',  "sql text", 'trim|required');
			$data = array();
			if ($this->form_validation->run() == FALSE)
			{
				
			}
			else
			{
				$this->load->library('Tablebuilder');
				$this->tablebuilder->set_query($_POST["sql"]);
				
				
				$data["result"] = 	$this->tablebuilder->get_table();
			} 
			
			$this->template->load('admin/themes',"report_builder/query_form",$data);
		
	}
	public function all_report()
	{
		$data["rows"] = $this->db->get("report_builders")->result_array();
		$this->user->save_action();
		$this->template->load('admin/themes',"report_builder/report_list",$data);
		$this->user->save_action();
	}
	
}
?>